# _*_ coding:utf-8 _*_
# @Time  : 2023.05.17
# @Author: zizlee
import pathlib
import datetime
from typing import List
from enum import Enum
from fastapi import APIRouter, Query, Body, Form, UploadFile
from fastapi.encoders import jsonable_encoder
from pydantic import BaseModel, validator, conint
from response import APIResponse
from utils import datetime_utils, file_utils
from utils.hutool_utils import StringUtils
from utils.ths_api import THSApi
from db_utils import BaseDatabaseConnection, DepartmentDataBaseConnection
from settings import FILE_STORAGE_HOST, STATIC_HOST


class FutureSpotBasicItem(BaseModel):
    close: float
    close_change: float
    spot: float
    spot_change: float


class CreateFutureSpotBasicItem(FutureSpotBasicItem):
    date: str
    name: str
    code: str

    @validator('date')
    def validate_start_date(cls, value):
        sd = datetime_utils.auth_yyyy_mm_dd(value)
        if not sd:
            raise ValueError('date invalidate! must be `yyyy-MM-dd`')
        return value


class UpdateFutureSpotBasicItem(FutureSpotBasicItem):
    rid: int
    is_active: conint(ge=0, le=1)


class FuturePositionItem(BaseModel):
    long: float
    long_change: float
    short: float
    short_change: float


class CreateFuturePositionItem(FuturePositionItem):
    date: str
    name: str
    code: str

    @validator('date')
    def validate_start_date(cls, value):
        sd = datetime_utils.auth_yyyy_mm_dd(value)
        if not sd:
            raise ValueError('date invalidate! must be `yyyy-MM-dd`')
        return value


class UpdateFuturePositionItem(FuturePositionItem):
    rid: int
    is_active: conint(ge=0, le=1)


class CandleItem(BaseModel):
    time: str
    code: str
    open: float
    close: float
    low: float
    high: float

    @validator('time')
    def validate_start_date(cls, value):
        sd = datetime_utils.auth_yyyy_mm_dd(value)
        if not sd:
            raise ValueError('time invalidate! must be `yyyy-MM-dd`')
        return value


class CreateCandleItem(CandleItem):
    pass


class PeriodReportTypes(Enum):
    ALL = ''  # 所有
    EARLY = 'EARLY'  # 晨报
    DAILY = 'DAILY'  # 日报
    WEEKLY = 'WEEKLY'  # 周报
    MONTHLY = 'MONTHLY'  # 月报
    ANNUAL = 'ANNUAL'  # 年报
    TECHNICAL = 'TECHNICAL'  # 技术解盘
    OTHERS = 'OTHERS'  # 其他


class PeriodReportItem(BaseModel):
    file_date: str

    @validator('file_date')
    def validate_start_date(cls, value):
        sd = datetime_utils.auth_yyyy_mm_dd(value)
        if not sd:
            raise ValueError('file_date invalidate! must be `yyyy-MM-dd`')
        return value


class CreatePeriodReportItem(PeriodReportItem):
    variety_en: str
    title: str
    file_type: PeriodReportTypes
    filepath: str


class CreateResearchFileItem(CreatePeriodReportItem):
    variety_en: str = None
    varieties: List[str]


class UpdatePeriodReportItem(PeriodReportItem):
    rid: int
    is_active: conint(ge=0, le=1)

# ------------------------------------ API操作类


class FutureSpot:
    def __init__(self, date: str = None):
        self.date = datetime_utils.auth_yyyymmdd(date, obj=True) if date else None

    def create_spot_basic(self, basics: List[CreateFutureSpotBasicItem]):
        basics_data = jsonable_encoder(basics)
        sql = """
            INSERT IGNORE INTO quote_future_spot 
            (date,code,name,close,close_change,spot,spot_change) VALUES 
            (%(date)s,%(code)s,%(name)s,%(close)s,%(close_change)s,%(spot)s,%(spot_change)s);
        """
        with BaseDatabaseConnection() as conn:
            count = conn.cursor.executemany(sql, basics_data)
        return APIResponse.success(msg='添加成功,新增{}个'.format(count))

    def daily_spot_basic(self):
        if not self.date:
            return APIResponse.validate_error(detail='查询参数date格式错误.')
        sql = """
            SELECT id,date,code,name,close,close_change,spot,spot_change,is_active FROM quote_future_spot
            WHERE date=%s ORDER BY code;
        """
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute(sql, [datetime_utils.to_yyyy_mm_dd(self.date)])
            basic = list(conn.cursor.fetchall())
        res_data = {
            'basic': basic
        }
        return APIResponse.success(data=res_data)

    def update_spot_basic(self, basics: List[UpdateFutureSpotBasicItem]):
        basics_data = jsonable_encoder(basics)
        sql = """
            UPDATE quote_future_spot SET close=%(close)s,close_change=%(close_change)s,spot=%(spot)s,
            spot_change=%(spot_change)s,is_active=%(is_active)s
            WHERE id=%(rid)s;
        """
        with BaseDatabaseConnection() as conn:
            count = conn.cursor.executemany(sql, basics_data)
        return APIResponse.success(msg='更新基差数据成功，变更数据{}行'.format(count))


class FuturePosition:
    def __init__(self, date: str = None):
        self.date = datetime_utils.auth_yyyymmdd(date, obj=True) if date else None

    def create_position(self, positions: List[CreateFuturePositionItem]):
        position_data = jsonable_encoder(positions)
        sql = """
            INSERT IGNORE INTO quote_future_position 
            (date,code,name,`long`,long_change,`short`,short_change) VALUES 
            (%(date)s,%(code)s,%(name)s,%(long)s,%(long_change)s,%(short)s,%(short_change)s);
        """
        with BaseDatabaseConnection() as conn:
            count = conn.cursor.executemany(sql, position_data)
        return APIResponse.success(msg='添加持仓数据成功,新增{}个'.format(count))

    def daily_position(self):
        if not self.date:
            return APIResponse.validate_error(detail='查询参数date格式错误.')
        sql = """
            SELECT id,date,code,name,`long`,long_change,`short`,short_change,is_active FROM quote_future_position
            WHERE date=%s ORDER BY code;
        """
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute(sql, [datetime_utils.to_yyyy_mm_dd(self.date)])
            basic = list(conn.cursor.fetchall())
        res_data = {
            'positions': basic
        }
        return APIResponse.success(data=res_data)

    def update_position(self, positions: List[UpdateFuturePositionItem]):
        position_data = jsonable_encoder(positions)
        sql = """
            UPDATE quote_future_position SET `long`=%(long)s,long_change=%(long_change)s,`short`=%(short)s,
            short_change=%(short_change)s,is_active=%(is_active)s
            WHERE id=%(rid)s;
        """
        with BaseDatabaseConnection() as conn:
            count = conn.cursor.executemany(sql, position_data)
        return APIResponse.success(msg='更新持仓数据成功，变更数据{}行'.format(count))


class BondCandle:
    def __init__(self, symbol: str = None):
        self.symbol = symbol

    def create_candles(self, items: List[CreateCandleItem]):
        data_list = jsonable_encoder(items)
        sql = """
            INSERT IGNORE quote_candle_daily (`time`,`code`,`open`,`close`,`low`,`high`) VALUES (%(time)s,%(code)s,
            %(open)s,%(close)s,%(low)s,%(high)s);
        """
        with BaseDatabaseConnection() as conn:
            count = conn.cursor.executemany(sql, data_list)
        return APIResponse.success(msg='添加日K行情成功，新增{}个数据。'.format(count))

    def retrieve_candles(self, page: int = 1, page_size: int = 50):
        if not self.symbol:
            return APIResponse.validate_error(detail='查询参数`symbol`必须!')
        sql = "SELECT id,`time`,`code`,`open`,`close`,`low`,`high` FROM quote_candle_daily WHERE code=%s ORDER BY `time` DESC;"
        with BaseDatabaseConnection() as conn:
            ret = conn.query_paginator(sql, [self.symbol], page=page, page_size=page_size)
        return APIResponse.success(data=ret)

    def real_quotes(self, start: str, end: str):
        start_date = datetime_utils.auth_yyyymmdd(start, obj=True)
        if not start_date:
            return APIResponse.validate_error(detail='start参数错误,接受YYYYMMDD')
        start_date = datetime_utils.to_yyyy_mm_dd(start_date)
        end_date = datetime_utils.auth_yyyymmdd(end, obj=True)
        if not end_date:
            end_date = datetime_utils.now_yyyy_mm_dd()
        else:
            end_date = datetime_utils.to_yyyy_mm_dd(end_date)
        if start_date > end_date:
            return APIResponse.validate_error(detail='start参数不合法.')

        ths_api = THSApi()
        data = ths_api.get_candle_daily(codes=self.symbol.split(','), indicators=['open', 'close', 'low', 'high'],
                                        start_date=start_date, end_date=end_date)
        res_data = {
            'symbol': self.symbol.split(','),
            'data': data
        }
        return APIResponse.success(data=res_data)


class PeriodReport:
    def __init__(self, report_type: PeriodReportTypes = ''):
        self.file_type = report_type

    def retrieve_period_report(self, page: int, page_size: int):
        sql = """
            SELECT id,file_date,title,variety_en,file_type,filepath,suffix,reading,is_location,is_active 
            FROM infor_periodic_report
        """
        prm = []
        if self.file_type.value:
            sql += ' WHERE file_type=%s'
            prm.append(self.file_type.value)
        sql += ' ORDER BY file_date DESC;'
        with BaseDatabaseConnection() as conn:
            ret = conn.query_paginator(sql, param=prm, page=page, page_size=page_size)
        for row in ret['rows']:
            if row['is_location']:
                row['file_url'] = STATIC_HOST + row['filepath']
            else:
                row['file_url'] = FILE_STORAGE_HOST + row['filepath']
        return APIResponse.success(data=ret)

    def create_period_report(self, reports: List[CreatePeriodReportItem]):
        report_list = jsonable_encoder(reports)
        # 找出filepath=''的记录(需查找处理路径的报告)
        save_list = []
        with DepartmentDataBaseConnection() as conn:
            for row in report_list:
                row['title'] = StringUtils.full2half(row['title'])
                if not row['filepath'].strip():
                    conn.cursor.execute("SELECT id,filepath FROM report_periodic_file WHERE file_date=%s AND variety_en=%s AND file_type=%s LIMIT 1;",
                                        [row['file_date'].replace('-', ''), row['variety_en'], row['file_type']])
                    f_obj = conn.cursor.fetchone()
                    if f_obj:
                        row['filepath'] = f_obj['filepath']
                        save_list.append(row)
                else:
                    save_list.append(row)
        if len(save_list) < 1:
            return APIResponse.no_content(msg='添加定期报告完成，没有检测到需要入库的记录!', data=report_list)
        sql = """
            INSERT IGNORE INTO infor_periodic_report (file_date,variety_en,title,file_type,filepath) VALUES (
            %(file_date)s,%(variety_en)s,%(title)s,%(file_type)s,%(filepath)s);
        """
        with BaseDatabaseConnection() as conn:
            count = conn.cursor.executemany(sql, save_list)
        return APIResponse.success(msg='添加定期报告完成，新增条目{}个.'.format(count))

    def update_period_report(self, reports: List[UpdatePeriodReportItem]):
        report_list = jsonable_encoder(reports)
        sql = "UPDATE infor_periodic_report SET is_active=%(is_active)s WHERE id=%(rid)s;"
        with BaseDatabaseConnection() as conn:
            count = conn.cursor.executemany(sql, report_list)
        return APIResponse.success(msg='修改定期报告完成，改变条目{}个.'.format(count))

    def create_research_file(self, file_item: CreateResearchFileItem):
        title = StringUtils.full2half(file_item.title)
        title = title.replace('.docx', '').replace('.doc', '')
        for i in range(10):
            title = title.replace(f'({i})', '')
        suffix = pathlib.Path(file_item.filepath).suffix.replace('.', '').upper()
        sql = """
           INSERT INTO infor_periodic_report (file_date,variety_en,title,file_type,filepath,suffix) VALUES (
           %s,%s,%s,%s,%s,%s);
       """
        prm = [file_item.file_date, ','.join(file_item.varieties), title, file_item.file_type.value, file_item.filepath,
               suffix]
        with BaseDatabaseConnection() as conn:
            count = conn.cursor.execute(sql, prm)
        return APIResponse.success(msg=f'增加{file_item.file_type.value}完成，改变条目{count}个.')

    async def upload_research_file(self, file: UploadFile, varieties: List[str] = Form(),
                                   file_type: PeriodReportTypes = Form(...), file_date: str = Form(...)):
        if not file.filename.lower().endswith('.pdf'):
            return APIResponse.validate_error(detail='文件格式有误')
        file_date = datetime_utils.auth_yyyy_mm_dd(file_date, obj=True)
        if not file_date:
            return APIResponse.validate_error(detail='日期格式错误!')
        # 文件保存
        folder = pathlib.Path('file/report/{}/{}/'.format(file_type.value, file_date.strftime('%Y-%m')))
        addr_info = file_utils.generate_unique_fileinfo(folder, filename=file.filename, suffix='.pdf', hashed=True)
        # 保存文件
        with open(addr_info['filepath'], 'wb') as fp:
            content = await file.read()
            fp.write(content)
        # 写入sql
        sql_path = folder.joinpath('{}{}'.format(addr_info['filename'], addr_info['suffix']))
        sql_path = str(sql_path).replace('\\', '/')
        sql = """
              INSERT INTO infor_periodic_report (file_date,variety_en,title,file_type,filepath,suffix,is_location) VALUES (
              %s,%s,%s,%s,%s,%s,%s);
          """
        prm = [file_date.strftime('%Y-%m-%d'), ','.join(varieties), file.filename, file_type.value, sql_path,
               'PDF', 1]
        with BaseDatabaseConnection() as conn:
            count = conn.cursor.execute(sql, prm)
        return APIResponse.success(msg=f'增加{file_type.value}完成，改变条目{count}个.')

    def department_work_file_list(self, date: str):  # 查询工作系统的报告
        sql = """
            SELECT id,create_time,file_date,file_type,creator_id,variety_en,title,filepath
            FROM report_periodic_file WHERE file_date=%s AND is_delete=0;
        """
        with DepartmentDataBaseConnection() as conn:
            conn.cursor.execute(sql, [date.replace('-', '')])
            file_list = list(conn.cursor.fetchall())
        return APIResponse.success(data={'files': file_list})

    def count_research_file(self):
        start_date = datetime_utils.interval_days(datetime.date.today(), days=-90, link='-', obj=False)
        # 查询总量
        with BaseDatabaseConnection() as conn:
            conn.cursor.execute("SELECT count(id) As total FROM infor_periodic_report;")
            total_count = conn.cursor.fetchone()['total']
            # 查询近三个月(180天)每日的数量
            conn.cursor.execute("SELECT file_date,count(id) As total FROM infor_periodic_report WHERE file_date>=%s GROUP BY file_date ORDER BY file_date;", [start_date])
            date_counts = conn.cursor.fetchall()
        return APIResponse.success(data={'total': total_count, 'date_counts': date_counts})


daily_maintain_api = APIRouter()


@daily_maintain_api.post('/spotBasic/create/')  # 保存新的数据
def create_daily_spot_basic(basics: List[CreateFutureSpotBasicItem] = Body(...)):
    return FutureSpot().create_spot_basic(basics=basics)


@daily_maintain_api.get('/spotBasic/retrieve/')  # 查询指定日期现货基差
def get_daily_spot_basic(date: str = Query(...)):
    return FutureSpot(date=date).daily_spot_basic()


@daily_maintain_api.put('/spotBasic/update/')  # 更新现货基差数据
def update_daily_spot_basic(basics: List[UpdateFutureSpotBasicItem] = Body(...)):
    return FutureSpot().update_spot_basic(basics=basics)


@daily_maintain_api.post('/position/create/')  # 新增持仓数据
def create_daily_position(positions: List[CreateFuturePositionItem]):
    return FuturePosition().create_position(positions=positions)


@daily_maintain_api.get('/position/retrieve/')  # 查询指定日期持仓
def get_daily_position(date: str = Query(...)):
    return FuturePosition(date=date).daily_position()


@daily_maintain_api.put('/position/update/')  # 更新持仓数据
def update_daily_position(positions: List[UpdateFuturePositionItem]):
    return FuturePosition().update_position(positions=positions)


@daily_maintain_api.get('/bondCandle/retrieve/')  # 证券的日k线数据
def create_candle(symbol: str = Query(...), page: int = Query(1), page_size: int = Query(50)):
    return BondCandle(symbol=symbol).retrieve_candles(page=page, page_size=page_size)


@daily_maintain_api.get('/bondCandle/quotes/')  # 证券的行情数据
def get_new_candle_data(symbol: str = Query(...), start: str = Query(...), end: str = Query(...)):
    return BondCandle(symbol=symbol).real_quotes(start=start, end=end)


@daily_maintain_api.post('/bondCandle/quotes/')  # 保存证券行情
def add_new_candle_data(items: List[CreateCandleItem] = Body(...)):
    return BondCandle().create_candles(items=items)


@daily_maintain_api.get('/periodReport/retrieve/')  # 查询定期报告
def retrieve_period_report(ft: PeriodReportTypes = Query(''), page: int = Query(1), page_size: int = Query(30)):
    return PeriodReport(report_type=ft).retrieve_period_report(page=page, page_size=page_size)


@daily_maintain_api.post('/periodReport/create/')  # 增加定期报告
def create_period_report(reports: List[CreatePeriodReportItem] = Body(...)):
    return PeriodReport().create_period_report(reports=reports)


@daily_maintain_api.put('/periodReport/update/')  # 修改定期报告
def update_period_report(reports: List[UpdatePeriodReportItem] = Body(...)):
    return PeriodReport().update_period_report(reports=reports)


# @daily_maintain_api.post('/periodReport/researchFile/create/')  # 新增研究文件
# def create_research_file(file: CreateResearchFileItem = Body(...)):
#     return PeriodReport().create_research_file(file_item=file)

@daily_maintain_api.get('/periodReport/departmentWorkFileList/')
def retrieve_department_work_file(date: str = Query(...)):
    return PeriodReport().department_work_file_list(date=date)


@daily_maintain_api.post('/periodReport/researchFile/upload/')  # 上传研究文件
async def upload_research_file(file: UploadFile = Form(...), varieties: List[str] = Form(...),
                               file_type: PeriodReportTypes = Form(...), file_date: str = Form(...)):
    return await PeriodReport().upload_research_file(file=file, varieties=varieties, file_type=file_type, file_date=file_date)


@daily_maintain_api.get('/periodReport/researchFile/count/')  # 查询研究文件数量
def count_research_file():
    return PeriodReport().count_research_file()
